# gen.rb
# usage:  ruby gen.rb schema.sql packageName

# generates EJB CMP entity beans and XML deployment descriptors from SQL Schema
# places java files in out/packageName
# places XML files in out/META-INF

# Programming by Eric Rollins
#
# Copyright (c) 2002 Eric Rollins
# www.acm.org/~rollins

# gen comes with Absolutely No Warranty.
# This is free software, and you are welcome to
# redistribute it under certain conditions
# (for details see:GNU General Public License,
# http://www.gnu.org/copyleft/gpl.html)

# version 0.1

#============================================================================
def to_java_name(sql_name)
  upNext = TRUE
  retString = ""

  sql_name = sql_name.sub(/ejb_/,"")

  sql_name.each_byte { |c|
    if c == '_'[0]
      upNext = TRUE
    else
      tmp = "" << c

      if upNext
        tmp.upcase!
        upNext = FALSE
      end

      retString << tmp
    end
  }

  return retString
end

#============================================================================
def downfirst(str)
  first = "" << str[0]
  rest = str[1..str.length]
  return first.downcase + rest
end

#============================================================================
class Column
  # Table object this column belongs to
  attr_reader :table
  # String name of Column in database
  attr_reader :name
  # String name of type from database (currently "int", "varchar")
  attr_reader :sql_type
  attr_writer :sql_type
  # is column unique (TRUE or FALSE)
  attr_reader :unique
  attr_writer :unique
  # is column primary key (TRUE or FALSE)
  attr_reader :primary_key
  attr_writer :primary_key
  # is column not null (TRUE or FALSE)
  attr_reader :not_null
  attr_writer :not_null
  # Column object this column as a foreign key points to, or nil
  attr_reader :references
  attr_writer :references
  # String java translation of column name
  attr_reader :java_name
  attr_writer :java_name
  # String java type for this column (currently "int", "String", "Integer")
  attr_reader :java_type
  attr_writer :java_type

  def initialize(table,name)
    @table = table
    @name = name
    @java_name = to_java_name(name)
  end

  def print
    if references
      reftable = references.table.name
      refcolumn = references.name
    end

    Kernel.print "  ",@name, " java_name=", @java_name, " sql_type=",@sql_type, " unique=", @unique, " primary_key=", @primary_key, " not_null=", @not_null, " references=", reftable, "(",refcolumn, ")\n"
  end
end

#============================================================================
class Table
  # String name of Table in database
  attr_reader :name
  # Hash of Column objects belonging to this Table
  attr_reader :columns
  # Hash of Column objects belonging to other Tables which point to this Table
  attr_reader :referred_by
  # Array of Column objects belonging to this Table
  # (used to maintain columns in order from database)
  attr_reader :columnsArray
  # String java translation of Table name
  attr_reader :java_name 

  def initialize(name)
    @name = name
    @columns = {}
    @referred_by = {}
    @columnsArray = []
    @java_name = to_java_name(name)
  end

  def addColumn(column)
    @columns[column.name] = column
    @columnsArray.push(column)
  end

  def print
    Kernel.print "\n================================\n"
    Kernel.print @name, "(",@java_name,")\n"
    @columnsArray.each {|col| col.print }
    Kernel.print "referred_by:\n"
    @referred_by.each {|rbn, rb| 
      Kernel.print "  ",rb.table.name,"(",rb.name,")\n" 
    }
  end
end

#============================================================================
def write_entity(table)
  className = table.java_name + "Entity"
  fileName = JAVA_DIR + className + ".java"
  f = File.new(fileName,"w");
  f.print "// ", className,".java\n\n"
  f.print "package ", $packageName,";\n\n"
  f.print "import javax.ejb.EJBLocalObject;\n"
  f.print "import javax.ejb.EJBException;\n\n"

  f.print "public interface ", className, " extends EJBLocalObject {\n"

  table.columnsArray.each {|column|
    f.print "  public ", column.java_type, " get", column.java_name, 
      "() throws EJBException;\n"
    f.print "  public void set", column.java_name, 
      "(", column.java_type, " ", downfirst(column.java_name), 
      ") throws EJBException;\n"
  }

  f.print("}\n")
  f.close()
end

#============================================================================
def write_entity_home(table)
  className = table.java_name + "Entity"
  homeName = className + "Home"
  fileName = JAVA_DIR + homeName + ".java"
  f = File.new(fileName,"w");
  f.print "// ", homeName,".java\n\n"
  f.print "package ", $packageName,";\n\n"
  f.print "import javax.ejb.EJBLocalHome;\n"
  f.print "import javax.ejb.EJBException;\n"
  f.print "import java.io.Serializable;\n"
  f.print "import javax.ejb.CreateException;\n"
  f.print "import javax.ejb.FinderException;\n\n"

        
  f.print "public interface ", homeName, " extends EJBLocalHome {\n"
  f.print "  public ", className, " create("
  first = TRUE

  table.columnsArray.each {|column|
    # next if column.references
    next if column.primary_key

    if first
      first = FALSE
    else
      f.print ", " 
    end

    f.print column.java_type, " ", downfirst(column.java_name)
  }

  f.print ")\n    throws EJBException, CreateException;\n"
  f.print "  public ", className, " findByPrimaryKey(Integer id)\n"
  f.print "    throws FinderException, EJBException;\n"
  f.print "  public java.util.Collection findAll()\n"
  f.print "    throws FinderException, EJBException;\n"

  f.print("}\n")
  f.close()
end

#============================================================================
def write_entity_bean(table)
  className = table.java_name + "EntityBean"
  fileName = JAVA_DIR + className + ".java"
  f = File.new(fileName,"w");
  f.print "// ", className,".java\n\n"
  f.print "package ", $packageName,";\n\n"
  f.print "import javax.ejb.EntityBean;\n"
  f.print "import javax.ejb.EntityContext;\n"
  f.print "import dwarf.Sequence;\n\n"

  f.print "public abstract class ", className, " implements EntityBean {\n"
  f.print "  transient private EntityContext ctx;\n\n"
  # -----------
  # ejbCreate
  f.print "  public Integer ejbCreate("
  first = TRUE

  table.columnsArray.each {|column|
    # next if column.references
    next if column.primary_key

    if first
      first = FALSE
    else
      f.print ", " 
    end

    f.print column.java_type, " ", downfirst(column.java_name)
  }

  f.print "){\n"

  table.columnsArray.each {|column|
    next if column.references

    f.print "    set",column.java_name,"("

    if column.primary_key
      f.print "new Integer(Sequence.next(\"",table.name,"_",column.name,
        "_seq\")));\n"
    else
      f.print downfirst(column.java_name),");\n"
    end
  }

  f.print "    return null;\n"
  f.print "  }\n\n"

  # -----------
  # ejbPostCreate
  f.print "  public void ejbPostCreate("
  first = TRUE

  table.columnsArray.each {|column|
    # next if column.references
    next if column.primary_key

    if first
      first = FALSE
    else
      f.print ", " 
    end

    f.print column.java_type, " ", downfirst(column.java_name)
  }

  f.print "){\n"

  table.columnsArray.each {|column|
    next if !column.references

    f.print "    set",column.java_name,"("
    f.print downfirst(column.java_name),");\n" 
  }

  f.print "  }\n\n"

  #----------
  table.columnsArray.each {|column|
    f.print "  public abstract ", column.java_type, " get", column.java_name, 
      "();\n"
    f.print "  public abstract void set", column.java_name, 
      "(", column.java_type, " ", downfirst(column.java_name), 
      ");\n"
  }

  f.print "  public void ejbRemove() {}\n"
  f.print "  public void ejbActivate() {}\n"
  f.print "  public void ejbPassivate() {}\n"
  f.print "  public void setEntityContext(EntityContext ctx) { this.ctx = ctx; }\n"
  f.print "  public void unsetEntityContext() { this.ctx = null; }\n"
  f.print "  public void ejbLoad() {}\n"
  f.print "  public void ejbStore() {}\n"
  f.print "}\n"
  f.close()
end

#============================================================================
def write_xml_jar(tables)  
  f = File.new(XML_DIR + "ejb-jar.xml.frag", "w")
  f.print "  <enterprise-beans>\n"

  tables.each { |tablename, table|  
    f.print "    <entity>\n"
    f.print "      <ejb-name>",table.java_name,"EntityEJB</ejb-name>\n"
    f.print "      <local>",$packageName,".",table.java_name,
                     "Entity</local>\n"
    f.print "      <local-home>",$packageName,".",table.java_name,
                     "EntityHome</local-home>\n"
    f.print "      <ejb-class>",$packageName,".",table.java_name,
                     "EntityBean</ejb-class>\n"
    f.print "      <persistence-type>Container</persistence-type>\n"
    f.print "      <prim-key-class>java.lang.Integer</prim-key-class>\n"
    f.print "      <reentrant>False</reentrant>\n"
    f.print "      <cmp-version>2.x</cmp-version>\n"
    f.print "      <abstract-schema-name>",table.java_name,
                     "</abstract-schema-name>\n"

    table.columnsArray.each {|column|
      next if column.references

      f.print "      <cmp-field><field-name>", downfirst(column.java_name),
                       "</field-name></cmp-field>\n"
    }

    table.columnsArray.each {|column|
      next if !column.primary_key
    
      f.print "      <primkey-field>", downfirst(column.java_name),
                       "</primkey-field>\n"
    }

    table.columnsArray.each {|column|
      next if !column.references
   
      f.print "      <ejb-local-ref>\n"
      f.print "         <ejb-ref-name>",$packageName,"/",
                          column.references.table.java_name,
                          "Entity</ejb-ref-name>\n"
      f.print "         <ejb-ref-type>Entity</ejb-ref-type>\n"
      f.print "         <local-home>",$packageName,".",
                          column.references.table.java_name,
                          "EntityHome</local-home>\n"
      f.print "         <local>",$packageName,".",
                          column.references.table.java_name,"Entity</local>\n"
      f.print "         <ejb-link>",column.references.table.java_name,
                          "EntityEJB</ejb-link>\n"
      f.print "       </ejb-local-ref>\n"
    }

    f.print "      <query>\n"
    f.print "        <query-method>\n"
    f.print "          <method-name>findAll</method-name>\n"
    f.print "          <method-params/>\n"
    f.print "        </query-method>\n"
    f.print "        <ejb-ql>\n"
    f.print "          SELECT OBJECT(o)\n"
    f.print "          FROM ", table.java_name, " o\n"
    f.print "        </ejb-ql>\n"
    f.print "      </query>\n"
    f.print "    </entity>\n"
  }

  f.print "  </enterprise-beans>\n"
  f.print "  <relationships>\n"

  tables.each { |tablename, table|
    table.columnsArray.each {|column|
      next if !column.references
      
      f.print "    <ejb-relation>\n"
      f.print "      <ejb-relation-name>",column.java_name,
                       "</ejb-relation-name>\n"
      f.print "      <ejb-relationship-role>\n"
      f.print "        <ejb-relationship-role-name>",
                         downfirst(table.java_name),"-has-",
                         downfirst(column.java_name),
                         "</ejb-relationship-role-name>\n"
      f.print "        <multiplicity>Many</multiplicity>\n"
      f.print "        <relationship-role-source>\n"
      f.print "          <ejb-name>",table.java_name,
                            "EntityEJB</ejb-name>\n"
      f.print "        </relationship-role-source>\n"
      f.print "        <cmr-field>\n"
      f.print "          <cmr-field-name>",downfirst(column.java_name),
                           "</cmr-field-name>\n"
      f.print "        </cmr-field>\n"
      f.print "      </ejb-relationship-role>\n"
      f.print "      <ejb-relationship-role>\n"
      f.print "        <ejb-relationship-role-name>",
                         downfirst(column.java_name),"-of-",
                         downfirst(table.java_name),
                         "</ejb-relationship-role-name>\n"
      f.print "        <multiplicity>One</multiplicity>\n"
      f.print "        <relationship-role-source>\n"
      f.print "          <ejb-name>",column.references.table.java_name,
                           "EntityEJB</ejb-name>\n"
      f.print "        </relationship-role-source>\n"
      f.print "      </ejb-relationship-role>\n"
      f.print "    </ejb-relation>\n"
    }
  }

  f.print "  </relationships>\n"
  f.print "  <assembly-descriptor>\n"

  tables.each { |tablename, table|
    f.print "      <container-transaction>\n"
    f.print "         <method>\n"
    f.print "            <ejb-name>",table.java_name,
                           "EntityEJB</ejb-name>\n"
    f.print "            <method-name>*</method-name>\n"
    f.print "         </method>\n"
    f.print "         <trans-attribute>Required</trans-attribute>\n"
    f.print "      </container-transaction>\n"
  }

  f.print "  </assembly-descriptor>\n"
  f.close()
end

#============================================================================
def write_jboss_xml(tables) 
  f = File.new(XML_DIR + "jboss.xml.frag", "w")
  f.print "  <enterprise-beans>\n"
                          
  tables.each { |tablename, table|
    f.print "    <entity>\n"
    f.print "      <ejb-name>",
                     table.java_name,"EntityEJB</ejb-name>\n"
    f.print "      <local-jndi-name>",$packageName,"/",table.java_name,
                     "Entity</local-jndi-name>\n"
    f.print "    </entity>\n"
  }

  f.print "  </enterprise-beans>\n"
  f.close()
end 

#============================================================================
def write_jbosscmp_jdbc_xml(tables)
  f = File.new(XML_DIR + "jbosscmp-jdbc.xml.frag", "w")
  f.print "  <enterprise-beans>\n"

  tables.each { |tablename, table|
    f.print "    <entity>\n"
    f.print "      <ejb-name>",
                     table.java_name,"EntityEJB</ejb-name>\n"
    f.print "      <table-name>", table.name, "</table-name>\n"

    table.columnsArray.each {|column|
      next if column.references
    
      f.print "      <cmp-field>\n"
      f.print "        <field-name>", downfirst(column.java_name),
                         "</field-name>\n"
      f.print "        <column-name>", column.name,
                         "</column-name>\n"
      f.print "      </cmp-field>\n"
    }

    f.print "    </entity>\n"
  }   

  f.print "  </enterprise-beans>\n"
  f.print "  <relationships>\n"

  tables.each { |tablename, table|
    table.columnsArray.each {|column|
      next if !column.references

      f.print "    <ejb-relation>\n"
      f.print "      <ejb-relation-name>",column.java_name,
                       "</ejb-relation-name>\n"
      f.print "         <foreign-key-mapping/>\n"
      f.print "         <ejb-relationship-role>\n"
      f.print "           <ejb-relationship-role-name>",
                            downfirst(table.java_name),"-has-",
                            downfirst(column.java_name),
                            "</ejb-relationship-role-name>\n"
      f.print "            <key-fields/>\n"
      f.print "         </ejb-relationship-role>\n"
      f.print "         <ejb-relationship-role>\n"
      f.print "           <ejb-relationship-role-name>",
                            downfirst(column.java_name),"-of-",
                            downfirst(table.java_name),
                            "</ejb-relationship-role-name>\n"
      f.print "            <key-fields>\n"
      f.print "               <key-field>\n"
      f.print "                  <field-name>", 
                                   downfirst(column.references.java_name),
                                   "</field-name>\n"
      f.print "                  <column-name>",column.name,"</column-name>\n"
      f.print "               </key-field>\n"
      f.print "            </key-fields>\n"
      f.print "         </ejb-relationship-role>\n"
      f.print "      </ejb-relation>\n"
    }
  }

  f.print "  </relationships>\n"
  f.close()
end   


#============================================================================
def process_column(line,column)
  if line =~ /unique/i
    column.unique = TRUE
  end

  if line =~/primary key/i
    column.primary_key = TRUE
  end

  if line =~ /not null/i
    column.not_null = TRUE
  end

  if column.sql_type == "int"
    if column.primary_key
      column.java_type = "Integer"
    else
      column.java_type = "int"
    end
  elsif column.sql_type == "varchar"
    column.java_type = "String"
  end
end

#============================================================================
# constants 
REX_CREATE_TABLE = /create table ([\w|_]+)/i
REX_INT_COLUMN = /([\w|_]+)\s+int/i
REX_VARCHAR_COLUMN = /([\w|_]+)\s+varchar/i
REX_ALTER_TABLE = /alter table ([\w|_]+)/i
REX_CONSTRAINT = /constraint ([\w|_]+)\s+foreign key\s*\(([\w|_]+)/i
REX_REFERENCES = /references ([\w|_]+)\s*\(([\w|_]+)/i
REX_PRIMARY_KEY = /primary key \(([\w|_|,|\s]+)\)/i

tables = {}
current_table = nil
current_alter_table = nil
current_fk_column = nil

if ARGV.length != 2
  print "usage:  ruby gen.rb schema.sql packageName\n"
  exit
end

schema = File.open(ARGV[0],"r")
$packageName = ARGV[1]

JAVA_DIR = "out/" + $packageName + "/"
XML_DIR = "out/META-INF/"

schema.each_line {|line| 
  if line =~ REX_CREATE_TABLE
    current_table = Table.new($1)
    tables[$1] = current_table
  elsif line =~ REX_INT_COLUMN
    column = Column.new(current_table,$1)
    column.sql_type = "int"
    process_column(line,column)
    current_table.addColumn(column)
  elsif line =~ REX_VARCHAR_COLUMN
    column = Column.new(current_table,$1)
    column.sql_type = "varchar"
    process_column(line,column)
    current_table.addColumn(column)
  elsif line =~ REX_ALTER_TABLE
    current_alter_table = tables[$1]
  elsif line =~ REX_CONSTRAINT
    current_fk_column = current_alter_table.columns[$2]
  elsif line =~ REX_REFERENCES
    current_fk_column.references = tables[$1].columns[$2]
    fk_tname = $1
    current_fk_column.java_name = current_fk_column.java_name.sub(/id/i,"")
    current_fk_column.java_type = tables[fk_tname].java_name + "Entity"
    tables[fk_tname].referred_by[current_alter_table.name] = current_fk_column
  elsif line =~ REX_PRIMARY_KEY
    keys = $1.split(",")
    keys.each { |key| current_table.columns[key].primary_key = TRUE }
  end 
}

schema.close



tables.each { |tablename, table| table.print }
tables.each { |tablename, table| write_entity(table) }
tables.each { |tablename, table| write_entity_home(table) }
tables.each { |tablename, table| write_entity_bean(table) }
write_xml_jar(tables)
write_jboss_xml(tables)
write_jbosscmp_jdbc_xml(tables)